using System;
using System.IO;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.Diagnostics;
using System.Text.RegularExpressions;
using System.Collections.Specialized;
using System.Configuration;

using System.Data;
using System.Globalization;
namespace StockDownloader.Accessors
{
    public class EODDataAccessor 
    {
        CookieCollection cc = new CookieCollection();
        string userName;
        string password;
        string baseUrl = "http://www.eoddata.com";
        string dbConn;
        string ftpSite;
        string localPath;
        public EODDataAccessor(string userName, string password, string dbConn, string ftpSite, string localPath)
        {
            this.userName = userName;
            this.password = password;
            this.dbConn = dbConn;
            this.ftpSite = ftpSite;
            this.localPath = localPath;
        }
        #region IGetStockData Members


        #endregion
     
        // Download a list of history files
        // Download a list of EOD Data files
        public StringCollection GetEODDataFileList(string url)
        { 
            
            FtpWebRequest request = null;
            FtpWebResponse response = null;
            StringCollection sc = new StringCollection();
            StreamReader sr;
            request = (FtpWebRequest)FtpWebRequest.Create(new Uri(url));
            request.Credentials = createCredential();
            request.Method = WebRequestMethods.Ftp.ListDirectory;
            using(response = (FtpWebResponse)request.GetResponse())
            using (sr = new StreamReader(response.GetResponseStream()))
            {
                string line = sr.ReadLine();
                while (line != null)
                {
                    sc.Add(line);
                    line = sr.ReadLine();
                }

                
            }
            return sc;
            
        }
        // Download all eod data files
        public void DownloadAllEodFiles(StringCollection filenames, string ftpSite)
        {
            string localpath;
            foreach (string s in filenames)
            {
                if (Path.GetExtension(s) == ".txt")
                {
                    localpath = string.Format(@"I:\Projects\StockManager\Data\{0}",s );
                    if (File.Exists(localpath)) File.Delete(localpath);
                    DownloadSingleFTPFile(ftpSite + "/" + s,localpath );
                    Console.WriteLine(string.Format("Download {0}",s));
                }
            }
        }
        public void DownloadAllSplits(string ftpSite)
        {

        }
        public void GetExchanges()
        {
           
            if(this.dbConn ==null)
                dbConn = ConfigurationManager.ConnectionStrings["StocksDB"].ConnectionString;
            string trunSql = "Select * from Exchanges";
            if(ftpSite == null)
                ftpSite = ConfigurationManager.AppSettings["ftpSite"];
            if(localPath == null)
                localPath = ConfigurationManager.AppSettings["localPath"];

            DataSet ds = SqlHelper.ExecuteDataset(dbConn, System.Data.CommandType.Text, trunSql);
            DownloadSingleFTPFile(ftpSite + "/Names/Exchanges.txt", localPath + @"\Exchanges.txt");
            if(File.Exists(localPath + @"\Exchanges.txt"))
            {
                string[] sLines = File.ReadAllLines(localPath + @"\Exchanges.txt");
                string[] CodeNames;
                DataRow[] drMatch;
                string sInsert;
                for (int i = 1; i < sLines.Length; i++)
                {
                    CodeNames = sLines[i].Split('\t');
                    drMatch =  ds.Tables[0].Select("Code = '" + CodeNames[0] + "'" );
                    if (drMatch.Length == 0)
                    {
                        sInsert = string.Format("insert into Exchanges (Code,Name, CollectData) values('{0}','{1}','false')",CodeNames[0].Trim(),CodeNames[1].Trim());
                        SqlHelper.ExecuteNonQuery(dbConn, CommandType.Text, sInsert);
                    }
                }
            }

        }

        public void GetStocks()
        {
            if (this.dbConn == null)
                dbConn = ConfigurationManager.ConnectionStrings["StocksDB"].ConnectionString;
            string trunSql = "Select * from Exchanges";
            string nameFile;
            if (ftpSite == null)
                ftpSite = ConfigurationManager.AppSettings["ftpSite"];
            if (localPath == null)
                localPath = ConfigurationManager.AppSettings["localPath"];

            DataSet ds = SqlHelper.ExecuteDataset(dbConn, System.Data.CommandType.Text, trunSql);

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                try
                {
                    nameFile =  localPath + string.Format(@"\{0}.txt", dr["Code"].ToString().Trim());
                    if(File.Exists(nameFile)) File.Delete(nameFile);
                    if ((bool)dr["CollectData"])
                    {
                        DownloadSingleFTPFile(string.Format("{1}/Names/{0}.txt", dr["Code"].ToString().Trim(), ftpSite), nameFile);
                        if (File.Exists(nameFile))
                        {

                            string[] sLines = File.ReadAllLines(localPath + string.Format(@"\{0}.txt", dr["Code"].ToString().Trim()));
                            string[] stocks;

                            string insert, insertValues;
                            for (int i = 1; i < sLines.Length; i++)
                            {
                                stocks = sLines[i].Split('\t');
                                insert = "INSERT INTO [Stocks].[dbo].[Stocks] ([Name] ,[ExchangeID] ,[Description] ,[Sector],[Industry],[Shares],[PE],[EPS]) VALUES (";
                                if (stocks.Length > 2)
                                    insertValues = string.Format("'{0}',{1},'{2}','{3}','{4}',{5},{6},{7})", stocks[0].Trim(), dr["ID"], stocks[1].Replace("'", "''"), stocks[2].Replace("'", "''"), stocks[3].Replace("'", "''"), Convert.ToInt64(stocks[4] == "" ? "0" : stocks[4]), Convert.ToDecimal(stocks[5] == "" ? "0" : stocks[5]), Convert.ToDecimal(stocks[6] == "" ? "0" : stocks[6]));
                                else
                                    insertValues = string.Format("'{0}',{1},'{2}','{3}','{4}',{5},{6},{7})", stocks[0].Trim(), dr["ID"], stocks[1].Replace("'", "''"), "", 0, 0, 0, 0);
                                Console.WriteLine(insertValues);
                                insert += insertValues;
                                SqlHelper.ExecuteNonQuery(dbConn, CommandType.Text, insert);
                            }
                        }
                    }
                }
                catch (Exception err)
                {
                    Console.WriteLine(string.Format("Error processing {0}, {1}",dr["Code"],err.Message));
                }

            }

        }

        
        public StringCollection GetFTPExhangeHistoryList(string ExchangeName)
        {
            if (ftpSite == null)
                ftpSite = ConfigurationManager.AppSettings["ftpSite"];
            StringCollection sc = GetEODDataFileList(ftpSite + "/History");
            string[] loopSC = new string[sc.Count];
            sc.CopyTo(loopSC, 0);
            foreach (string s in loopSC)
            {
                if(!Path.GetFileName(s).StartsWith(ExchangeName))
                sc.Remove(s);
            }
            return sc;


        }

        public void DownloadHistoryData()
        {
            DataSet ds = GetCollectableExchanges();
            DataSet dsStockEx = GetStockExchange();
            if (this.dbConn == null)
                dbConn = ConfigurationManager.ConnectionStrings["StocksDB"].ConnectionString;
            if (ftpSite == null)
                ftpSite = ConfigurationManager.AppSettings["ftpSite"];
            if (localPath == null)
                localPath = ConfigurationManager.AppSettings["localPath"];
            string localFile;
            string insertSql = "INSERT INTO [Stocks].[dbo].[PriceData] ([StockID] ,[Date] ,[Open] ,[High] ,[Low],[Close] ,[Volume]) VALUES(";
            int StockId;
            StringBuilder insertValues;
            foreach (DataRow dr in ds.Tables["Exchanges"].Rows)
            {
                int ExchangeId = (int)dr["ID"];
                StringCollection sc = GetFTPExhangeHistoryList(dr["Code"].ToString());
                foreach (string sFtpFile in sc)
                {
                    localFile = string.Format(@"{0}\{1}", localPath,sFtpFile );
 //                   DownloadSingleFTPFile(string.Format("{0}/History/{1}",ftpSite,sFtpFile),localFile);
                    if (File.Exists(localFile))
                    {
                        //insert file contents
                        string[] sLines = File.ReadAllLines(localFile);
                        string[] sHeader = sLines[0].Split(',');
                        string ticker = "";
                        string period = "";
                        DateTime Date;
                        decimal open = 0.0M;
                        decimal high = 0.0M;
                        decimal low = 0.0M;
                        decimal close = 0.0M;
                        long vol = 0;
                        for(int iLine = 1; iLine < sLines.Length; iLine ++)
                        {
                            try
                            {

                                string[] sColumns = sLines[iLine].Split(',');
                                insertValues = new StringBuilder();
                                DataRow[] drLookup = dsStockEx.Tables["StockExchange"].Select("ExchangeID = " + ExchangeId.ToString() + " and  StockName= '" + sColumns[0] + "'");
                                if (drLookup.Length > 0)
                                {
                                    int iCountColumns = 0;

                                    ticker = sColumns[0];
                                    Date = DateTime.ParseExact(sColumns[1], "yyyyMMdd", new CultureInfo("en-US"));
                                    open = Convert.ToDecimal(sColumns[2] == "" ? "0" : sColumns[2]);
                                    high = Convert.ToDecimal(sColumns[3] == "" ? "0" : sColumns[3]);
                                    low = Convert.ToDecimal(sColumns[4] == "" ? "0" : sColumns[4]);
                                    close = Convert.ToDecimal(sColumns[5] == "" ? "0" : sColumns[5]);
                                    vol = Convert.ToInt64(sColumns[6] == "" ? "0" : sColumns[6]);
                                    insertValues.Append(string.Format("{0},'{1}',{2},{3},{4},{5},{6})", drLookup[0]["StockId"], Date.ToShortDateString(), open, high, low, close, vol));
                                    
                                    try
                                    {
                                        SqlHelper.ExecuteNonQuery(dbConn, CommandType.Text, insertSql + insertValues.ToString());
                                        Console.WriteLine(insertValues);
                                    }
                                    catch (System.Data.SqlClient.SqlException err)
                                    {
                                        Console.Error.WriteLine(err.Message + " " + insertValues);
                                    }

                                }
                            }
                            catch (Exception err)
                            {
                                Console.Error.WriteLine(err.Message + " on " + localFile + " line : " + iLine);
                            }
                            
                            

                        }
                    }
                }
            }

        }

        public DataSet GetCollectableExchanges()
        {
            string sql = "Select * from Exchanges where CollectData = 'true'";
            DataSet ds;
            if (this.dbConn == null)
                dbConn = ConfigurationManager.ConnectionStrings["StocksDB"].ConnectionString;
            ds = SqlHelper.ExecuteDataset(dbConn, CommandType.Text, sql);
            if (ds.Tables.Count > 0)
            {
                ds.Tables[0].TableName = "Exchanges";
                return ds;

            }
            else
                return null;
        }

        public DataSet GetStockExchange()
        {
            string sql = "Select * from vw_StockExchange";
            DataSet ds;
            if (this.dbConn == null)
                dbConn = ConfigurationManager.ConnectionStrings["StocksDB"].ConnectionString;
            
            ds = SqlHelper.ExecuteDataset(dbConn, CommandType.Text, sql);
            if (ds.Tables.Count > 0)
            {
                ds.Tables[0].TableName = "StockExchange";
                return ds;
            }
            else
                return ds;


        }

        public void DownloadSingleFTPFile(string url, string saveToPath)
        {
            FtpWebRequest request = null;
            
            StringCollection sc = new StringCollection();
            
            request = (FtpWebRequest)FtpWebRequest.Create(new Uri(url));
            request.Credentials = createCredential();
            request.Method = WebRequestMethods.Ftp.DownloadFile;
            using (WebResponse response = request.GetResponse())
            using (BinaryReader reader = new BinaryReader(response.GetResponseStream()))
            using (BinaryWriter writer = new BinaryWriter(File.Open(saveToPath, FileMode.Create)))
            {
                byte[] buffer = new byte[2048];
                int count = reader.Read(buffer, 0, buffer.Length);
                while (count != 0)
                {
                    writer.Write(buffer, 0, count);
                    count = reader.Read(buffer, 0, buffer.Length);
                }
            }
        }

        /// <summary>
        /// Create  a network credential with the correct user name & password.
        /// </summary>
        /// <returns></returns>
        public NetworkCredential createCredential()
        {
            NetworkCredential credential = new NetworkCredential(userName, password);
            return credential;
        }

        
        
    }
}

